package com.service.database.handler;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.util.StrUtil;
import com.service.database.config.DataBase;
import com.service.database.constants.JdbcConstants;
import com.service.database.constants.SqlConstants;
import com.service.database.model.DataColumn;
import com.service.database.utils.CommonUtils;

import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

/**
 * oracle处理器
 * @author xianggj
 * @Date 2021/6/4 21:44
 **/
public class OracleHandler extends AbstractDatabaseHandler {

    /**
     * 数值类型小数位最大精度和默认长度
     */
    public static final int MAX_NUMBER_LENGTH = 38;
    public static final int DEFAULT_NUMBER_LENGTH = 10;
    public static final int MAX_FLOAT_LENGTH = 127;
    public static final int DEFAULT_FLOAT_LENGTH = 0;

    public OracleHandler(DataBase type) {
        super(type);
    }

    /**
     * jdbc:oracle:thin:username/password@//x.x.x.1:1522/ABCD
      */
    @Override
    public String urlSpecialDeal(StringBuffer currentUrl) {
        if (StrUtil.isNotBlank(userName) && StrUtil.isNotBlank(pwd)){
            String userAndPwd = userName + JdbcConstants.DECOLLATOR + pwd;
            currentUrl = currentUrl.replace(0, 20, String.format("jdbc:oracle:thin:%s@//", userAndPwd));
        }
        if (CommonUtils.isNotBlank(schema)){
            return currentUrl + schema;
        } else {
            throw new IllegalArgumentException("URL中缺少服务名");
        }
    }

    @Override
    public String getShowDataBaseSql() {
        return String.format("SELECT USERNAME as %s FROM ALL_USERS", returnKeys);
    }

    /**
     *   ORACLE 只有用户概率 他的库相当于空间
     * @return
     */
    @Override
    public String getShowTablesSql() {
        return String.format("select t1.table_name as %s ,t2.comments as %s from all_tables " +
                "t1 left join user_tab_comments  t2 on t1.table_name = t2.table_name where t1.owner = ? ", returnKeys);
    }

    @Override
    public String getShowTableDescSql() {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT ");
        sql.append(String.format("utc.COLUMN_NAME AS %s," +
                "utc.DATA_TYPE AS %s," +
//                "utc.NULLABLE AS %s," +
                "CASE WHEN utc.NULLABLE = 'Y' THEN '是' ELSE '否' END AS %s," +
                "CASE WHEN cu.COLUMN_NAME = utc.COLUMN_NAME THEN '是' ELSE '否' END AS %s," +
                "CASE WHEN utc.DATA_TYPE = 'NUMBER' THEN utc.DATA_PRECISION ELSE utc.DATA_LENGTH END AS %s," +
                "NVL(utc.DATA_SCALE,0) AS %s," +
                "t.COMMENTS AS %s ",returnKeys));
        sql.append("FROM user_tab_columns utc " );
        //求注释
        sql.append("LEFT JOIN user_col_comments t ON t.COLUMN_NAME = utc.COLUMN_NAME " +
                "AND utc.TABLE_NAME = t.TABLE_NAME ");
        //求主键
        sql.append("LEFT JOIN ( SELECT cu.table_name AS table_name, " +
                "cu.COLUMN_NAME AS COLUMN_NAME  FROM " +
                "user_cons_columns cu,user_constraints au  " +
                "WHERE cu.constraint_name = au.constraint_name " +
                "AND au.constraint_type = 'P') cu ON utc.TABLE_NAME = cu.TABLE_NAME " +
                "AND utc.COLUMN_NAME = cu.COLUMN_NAME " );
        sql.append("WHERE utc.TABLE_NAME =? ");
        return sql.toString();
    }

    @Override
    public String getShowTableDataSql() {
        return String.format("select * from %s ",  addChar(tableName));
    }


    @Override
    public String getShowTableDataNumSql() {
        return String.format("select count(0) as %s from %s", returnKeys[0], addChar(tableName));
    }

    @Override
    public String distinctColSql() {

        StringBuffer sql = new StringBuffer("select distinct ");
        List<String> sqls = new ArrayList<>();
        for (int i = 0; i < cols.length; i++) {
            if (returnKeys.length > i){
                sqls.add(String.format(" %s as %s", cols[i], returnKeys[i]));
            } else {
                sqls.add(String.format(" %s", cols[i]));
            }
        }
        sql.append(CollUtil.join(sqls, SqlConstants.COMMA));
        sql.append(String.format(" from %s ", addChar(tableName)));
        return sql.toString();
    }

    @Override
    public String addLimitBySql(String sql, Integer num, Integer offset) {
        //oracle 分页 巨头疼
        //SELECT * FROM (SELECT ROWNUM AS rowno, t.* FROM TESTTREE t WHERE  ROWNUM <= 20)
        // table_alias WHERE table_alias.rowno >= 10;
        if (offset != null && num != null){
            sql = String.format(SqlConstants.SELECT_CONTAIN_ROWNUM, sql);
            num += offset;
        }
        String outSql = CommonUtils.substringNotBracketsString(sql);
        //如果包含limit
        if (StrUtil.containsIgnoreCase(outSql, SqlConstants.WHERE)){
            sql = SqlConstants.AND;
        } else {
            sql += SqlConstants.WHERE;
        }
        sql += SqlConstants.ROWNUM + SqlConstants.LESS_EQUELS +num;
        if (offset != null){
            sql = String.format(SqlConstants.SELECT_CONTAIN, sql);
            sql += SqlConstants.WHERE + SqlConstants.ROWNO + SqlConstants.MORE_EQUELS + offset;
        }
        return sql;
    }

    /**
     * declare
     *       num   number;
     * begin
     *     select count(1) into num from user_tables where table_name = '%s';
     *     if num > 0 then
     *         execute immediate 'DROP TABLE  %s.%s' ;
     *     end if;
     * end;
     * @return
     */
    @Override
    public String dropTableSql() {
        return String.format("declare" +
                        "       num  number;" +
                        "     begin" +
                        "       select count(1) into num from user_tables where table_name = '%s';" +
                        "       if num > 0 then" +
                        "          execute immediate 'DROP TABLE  %s.%s' ;" +
                        "       end if;" +
                        "     end; ",
                tableName, addChar(dbName), addChar(tableName));
    }

    @Override
    public List<String> createTableSql(List<DataColumn> columns) {
        String createSql = "" +
                "declare" +
                "   num  number;" +
                "   STRSQL_CREATE varchar2(1024);" +
                "begin" +
                "    select count(1) into num from user_tables where table_name = '%s';" +
                "    if num = 0 then" +
                "        STRSQL_CREATE := '%s';" +
                "        execute immediate STRSQL_CREATE ;" +
                "    end if;" +
                "end;";
        StringBuffer sql = new StringBuffer();
        sql.append("CREATE TABLE  ");
        String tableNameStr = String.format(" %s.%s ",
                addChar(dbName), addChar(this.tableName));
        sql.append(tableNameStr);
        sql.append("(");
        List<String> sqls = new ArrayList<>();
        for (int i = 0; i < columns.size() ; i++) {
            DataColumn dataCol = columns.get(i);
            String columnName = dataCol.getColumnName();
            int dataType = dataCol.getDataTypeInt();
            if (StrUtil.isBlank(columnName) || dataType == 0){
                continue;
            }
            //主键不添加 因为不能重复添加
//            if (dataCol.getColumnKey()){
//                //     * ALTER TABLE "public"."tb_student" ADD CONSTRAINT "tb_student_pkey" PRIMARY KEY ("id");
//                sqls.add(" ALTER TABLE  " +tableName + " ADD CONSTRAINT \""+ this.tableName +"_pkey\" PRIMARY KEY (" + addChar(columnName)+")");
//            }

            sql.append(addChar(columnName));
            sql.append(" ");
            addTypeStr(sql, dataCol);
            if (dataCol.getIsNull()){
                sql.append(" ");
            } else {
                sql.append(" NOT NULL ");
            }
            if (i != columns.size() -1){
                sql.append(",");
            }
            if (StrUtil.isNotBlank(dataCol.getColumnComment())){
                //     * COMMENT ON COLUMN "public"."tb_student"."id" IS '序号';
                sqls.add("COMMENT ON COLUMN "+ tableNameStr + "." + addChar(columnName) + " IS '"+StrUtil.removeAll(dataCol.getColumnComment(), '\'')+"'");
            }
        }
        sql.append(")");
        List<String> list = new ArrayList<>();
        list.add(String.format(createSql, tableName, sql.toString()));
        list.addAll(sqls);
        return list;
    }


    private void addTypeStr(StringBuffer sql, DataColumn dataCol) {
        String typeStr = intToType(dataCol.getDataTypeInt());
        Integer length = dataCol.getLength();
        Integer floatLength = dataCol.getFloatLength();
        switch (typeStr){
            case "VARCHAR2":
                sql.append(typeStr);
                sql.append("(");
                if (length==null || length == 0){
                    length = SqlConstants.DEFAULT_VARCHAR_LENGTH;
                }
                sql.append(length);
                sql.append(")");
                break;
            case "CHAR":
                sql.append(typeStr);
                sql.append("(");
                if (length==null || length == 0){
                    length = 5;
                }
                sql.append(length);
                sql.append(")");
                break;
            case "TIMESTAMP":
                sql.append(typeStr);
                sql.append("(6)");
                break;
            case "NUMBER":
                sql.append(typeStr);
                sql.append("(");
                if (length==null || length == 0){
                    length = DEFAULT_NUMBER_LENGTH;
                }
                //最大精度38
                if(length > MAX_NUMBER_LENGTH){
                    length = MAX_NUMBER_LENGTH;
                }

                sql.append(length);
                if (floatLength != null && floatLength != 0){
                    sql.append(",");
                    //最大精度127
                    if(floatLength > MAX_FLOAT_LENGTH){
                        floatLength = MAX_FLOAT_LENGTH;
                    }
                    sql.append(floatLength);
                } else {
                    sql.append(",");
                    sql.append(DEFAULT_FLOAT_LENGTH);
                }
                sql.append(")");
                break;
            default:
                sql.append(typeStr);
        }
    }


    @Override
    protected String intToType(int i) {
        switch (i){
            case Types.CHAR:
                return "CHAR";
            case Types.TIME:
            case Types.DATE:
            case Types.TIMESTAMP_WITH_TIMEZONE:
                return "DATE";
            case Types.TIMESTAMP:
                return "TIMESTAMP";
            case Types.TIME_WITH_TIMEZONE:
                return "TIMESTAMP WITH TIME ZONE";
            case Types.BIGINT:
            case Types.INTEGER:
            case Types.DOUBLE:
            case Types.DECIMAL:
            case Types.FLOAT:
            case Types.REAL:
                return "NUMBER";
            default:
                return "VARCHAR2";
        }
    }

}
